package com.boesch.electroOffice.dataaccess.daoImpl;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.joda.time.LocalDate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.TransactionCallbackWithoutResult;
import org.springframework.transaction.support.TransactionTemplate;

import com.boesch.electroOffice.dataaccess.common.IllegalDataAccessException;
import com.boesch.electroOffice.dataaccess.common.ReturnCodesDataaccess;
import com.boesch.electroOffice.dataaccess.dao.AppointmentDao;
import com.boesch.electroOffice.dataaccess.dao.IdentifierDao;
import com.boesch.electroOffice.dataaccess.mapper.AppointmentMapper;
import com.boesch.electroOffice.dataaccess.mapper.IdentifierMapper;
import com.boesch.electroOffice.model.electroOffice.Appointment;
import com.boesch.electroOffice.model.electroOffice.Identifier;
import com.boesch.electroOffice.utils.DateUtils;
import com.liferay.portal.kernel.log.Log;
import com.liferay.portal.kernel.log.LogFactoryUtil;
import com.liferay.portal.model.User;

/**
 * This class executes the sql statements on the electroOffice database for class Appointment.
 * If a Exception is thrown, 
 * it will be unpacked to a illegalDataAccessException 
 * including a return code which can be shown to the user.
 * 
 * @author Ralph Koster
 * 
 */
@Repository
public class AppointmentDaoImpl implements AppointmentDao, Serializable {

	private static final long serialVersionUID = -8371608951696523201L;
	private static final Log log = LogFactoryUtil.getLog(AppointmentDaoImpl.class);

	@Autowired
	private IdentifierDao identifierDao;
	@Autowired
	private JdbcTemplate eoJdbcTemplate;
	@Autowired
	private TransactionTemplate eoTransactionTemplate;

	@Value("${electroOffice.queryReadAllAppointments}")
	private String queryReadAllAppointments;
	@Value("${electroOffice.queryReadAppointmentsWhereUserHasWorkingHours}")
	private String queryReadAppointmentsWhereUserHasWorkingHours;
	@Value("${electroOffice.queryReadAppointmentById}")
	private String queryReadAppointmentById;
	@Value("${electroOffice.queryCreateAppointment}")
	private String queryCreateAppointment;
	@Value("${electroOffice.queryUpdateAppointment}")
	private String queryUpdateAppointment;
	@Value("${electroOffice.queryDeleteAppointmentJobIdentifiers}")
	private String queryDeleteAppointmentJobIdentifiers;
	@Value("${electroOffice.queryAddJobIdentifiersToAppointment}")
	private String queryAddJobIdentifiersToAppointment;
	@Value("${electroOffice.queryCreateAppointmentMaterial}")
	private String queryCreateAppointmentMaterial;
	@Value("${electroOffice.queryDeleteAppointmentMaterial}")
	private String queryDeleteAppointmentMaterial;

	// Queries for Identifiers(Table e_appointment_jobidentifiers)
	@Value("${electroOffice.queryReadIdentifiersByAppointmentId}")
	private String queryReadIdentifiersByAppointmentId;

	/**
	 * {@inheritDoc}
	 */
	public List<Appointment> readAllAppointments() throws IllegalDataAccessException {
		List<Appointment> appointments = new ArrayList<Appointment>();
		try {
			appointments = eoJdbcTemplate.query(queryReadAllAppointments, new AppointmentMapper(identifierDao));
			for (Appointment appointment : appointments) {
				List<Identifier> jobIdentifiers = eoJdbcTemplate.query(queryReadIdentifiersByAppointmentId, new Object[] { appointment.getId() }, new IdentifierMapper());
				appointment.setJobIdentifiers(jobIdentifiers);
			}
			return appointments;
		} catch (DataAccessException e) {
			throw new IllegalDataAccessException(ReturnCodesDataaccess.NO_OBJECTS_FOUND_ERROR.getValue(), e);
		}
	}

	/**
	 * {@inheritDoc}
	 */
	public Appointment readAppointmentById(Integer appointmentId) throws IllegalDataAccessException {
		Appointment appointment = null;
		List<Appointment> appointments = eoJdbcTemplate.query(queryReadAppointmentById, new Object[] { appointmentId }, new AppointmentMapper(identifierDao));
		if (null != appointments && appointments.size() == 1) {
			appointment = appointments.get(0);
			List<Identifier> jobIdentifiers = eoJdbcTemplate.query(queryReadIdentifiersByAppointmentId, new Object[] { appointment.getId() }, new IdentifierMapper());
			appointment.setJobIdentifiers(jobIdentifiers);
			return appointment;
		} else {
			throw new IllegalDataAccessException(ReturnCodesDataaccess.OBJECT_NOT_FOUND_ERROR.getValue());
		}
	}

	/**
	 * {@inheritDoc}
	 */
	public List<Appointment> readAppointmentsWhereUserHasWorkingHours(long userId, LocalDate dateOfMonday, LocalDate datePlusDays) throws IllegalDataAccessException {
		List<Appointment> appointments = new ArrayList<Appointment>();
		try {
			appointments = eoJdbcTemplate.query(queryReadAppointmentsWhereUserHasWorkingHours,
					new Object[] { userId, dateOfMonday.toString("yyyy-MM-dd"), datePlusDays.toString("yyyy-MM-dd") }, new AppointmentMapper(identifierDao));
			for (Appointment appointment : appointments) {
				List<Identifier> jobIdentifiers = eoJdbcTemplate.query(queryReadIdentifiersByAppointmentId, new Object[] { appointment.getId() }, new IdentifierMapper());
				appointment.setJobIdentifiers(jobIdentifiers);
			}
			return appointments;
		} catch (DataAccessException e) {
			throw new IllegalDataAccessException(ReturnCodesDataaccess.NO_OBJECTS_FOUND_ERROR.getValue(), e);
		}
	}

	/**
	 * {@inheritDoc}
	 */
	public String saveAppointmentOnDatabase(final Appointment appointment, final User currentUser) throws IllegalDataAccessException {
		final KeyHolder keyHolder = new GeneratedKeyHolder();
		try {
			eoTransactionTemplate.execute(new TransactionCallbackWithoutResult() {
				@Override
				public void doInTransactionWithoutResult(TransactionStatus status) {
					eoJdbcTemplate.update(new PreparedStatementCreator() {
						public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
							PreparedStatement ps = connection.prepareStatement(queryCreateAppointment, Statement.RETURN_GENERATED_KEYS);
							ps.setString(1, appointment.getReportNumber());
							ps.setInt(2, appointment.getProjectNumber());
							ps.setString(3, appointment.getSubject());
							ps.setString(4, appointment.getDescription());
							ps.setDate(5, DateUtils.convertLocalDateToDate(appointment.getRegistrationDate()));
							ps.setTimestamp(6, DateUtils.convertLocalDateTimeToTimestamp(appointment.getAppointmentDate()));
							ps.setString(7, appointment.getEmployer());
							ps.setInt(8, appointment.getIdCustomerAddress());
							ps.setInt(9, appointment.getIdInvoiceAddress());
							ps.setInt(10, appointment.getIdObjectAddress());
							ps.setLong(11, appointment.getLrUserId());
							ps.setInt(12, null != appointment.getState() ? appointment.getState().getId() : null);
							ps.setString(13, appointment.getPhoneCustomer());
							ps.setString(14, currentUser.getFirstName().substring(0, 0) + currentUser.getLastName().substring(0, 1));
							ps.setTimestamp(15, DateUtils.getCurrentTimestamp());
							ps.setInt(16, 1);
							return ps;
						}
					}, keyHolder);

					List<Identifier> jobIdentifiers = appointment.getJobIdentifiers();
					for (final Identifier jobIdentifier : jobIdentifiers) {
						eoJdbcTemplate.update(new PreparedStatementCreator() {
							public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
								PreparedStatement ps = connection.prepareStatement(queryAddJobIdentifiersToAppointment, Statement.RETURN_GENERATED_KEYS);
								ps.setInt(1, keyHolder.getKey().intValue());
								ps.setInt(2, jobIdentifier.getId());
								return ps;
							}

						});
					}
				}
			});
			log.debug("Inserted appointment with id: " + keyHolder.getKey());
			return ReturnCodesDataaccess.CREATED.getValue();
		} catch (DataAccessException e) {
			throw new IllegalDataAccessException(ReturnCodesDataaccess.CREATION_ERROR.getValue(), e);
		}
	}

	/**
	 * {@inheritDoc}
	 */
	public String updateAppointmentOnDatabase(final Appointment appointment, final User currentUser) throws IllegalDataAccessException {
		final KeyHolder keyHolder = new GeneratedKeyHolder();
		try {
			eoTransactionTemplate.execute(new TransactionCallbackWithoutResult() {
				@Override
				public void doInTransactionWithoutResult(TransactionStatus status) {
					eoJdbcTemplate.update(new PreparedStatementCreator() {
						public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
							PreparedStatement ps = connection.prepareStatement(queryUpdateAppointment, Statement.RETURN_GENERATED_KEYS);
							ps.setString(1, appointment.getReportNumber());
							ps.setInt(2, appointment.getProjectNumber());
							ps.setString(3, appointment.getSubject());
							ps.setString(4, appointment.getDescription());
							ps.setTimestamp(5, DateUtils.convertLocalDateTimeToTimestamp(appointment.getAppointmentDate()));
							ps.setString(6, appointment.getEmployer());
							ps.setInt(7, appointment.getIdCustomerAddress());
							ps.setInt(8, appointment.getIdInvoiceAddress());
							ps.setInt(9, appointment.getIdObjectAddress());
							ps.setString(10, appointment.getPhoneCustomer());
							ps.setLong(11, currentUser.getUserId());
							ps.setInt(12, null != appointment.getState() ? appointment.getState().getId() : null);
							ps.setString(13, currentUser.getFullName());
							ps.setInt(14, appointment.getVersion() + 1);
							ps.setInt(15, appointment.getId());
							return ps;
						}
					}, keyHolder);

					eoJdbcTemplate.update(queryDeleteAppointmentJobIdentifiers, appointment.getId());

					List<Identifier> jobIdentifiers = appointment.getJobIdentifiers();
					for (final Identifier jobIdentifier : jobIdentifiers) {
						eoJdbcTemplate.update(new PreparedStatementCreator() {
							public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
								PreparedStatement ps = connection.prepareStatement(queryAddJobIdentifiersToAppointment, Statement.RETURN_GENERATED_KEYS);
								ps.setInt(1, appointment.getId());
								ps.setInt(2, jobIdentifier.getId());
								return ps;
							}

						});
					}

					eoJdbcTemplate.update(queryDeleteAppointmentMaterial, appointment.getId());
				}
			});
			log.debug("Updated appointment with id: " + appointment.getId());
			return ReturnCodesDataaccess.UPDATED.getValue();
		} catch (DataAccessException e) {
			throw new IllegalDataAccessException(ReturnCodesDataaccess.UPDATE_ERROR.getValue(), e);
		}
	}
}
